/**
 * 
 */
package com.nari.heatshare.dao;

import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.joda.time.DateTime;
import org.joda.time.DateTimeFieldType;
import org.springframework.expression.spel.ast.Assign;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.orm.hibernate3.HibernateTemplate;

import com.nari.baseinfo.model.IHeatRoom;
import com.nari.baseinfo.model.IProject;
import com.nari.coreframe.dao.hibernate3.GenericDaoImpl;
import com.nari.coreframe.dao.springjdbc.extendcore.ResultSetMapHandler;
import com.nari.heatshare.model.IAssignAllot;
import com.nari.heatshare.model.IAssignMeterDataGather;
import com.nari.heatshare.utils.queryvo.AssignMeterDataCustomizedQuery;
import com.nari.platform.date.CalendarUtils;
import com.nari.platform.model.PageCond;
import com.nari.platform.model.ResponseMsg;
import com.nari.platform.model.ResultWithPageCond;
import com.nari.platform.sqlfunc.mysql.DateFunctionUtils4MySQL;
import com.nari.platform.utils.CommercialHighPrecisionArith;
import com.nari.platform.utils.NumberSystemConversion;
import com.nari.rescontroller.dao.IResourceAccessControlDao;
import com.nari.sink.dao.ISinkDao;
import com.sohoometer.web.bean.AssignDataWithInfo;

/**
 * 
 * @author Xiaopeng
 * @date 2013-8-18
 */
public class AssignMeterDataGatherDaoImpl extends GenericDaoImpl<IAssignMeterDataGather, String> 
		implements IAssignMeterDataGatherDao {
	//数据库本地化配置
	private String sysDbName;
	private String oracleDbName;
	private String mysqlDbName;
	private IResourceAccessControlDao iResourceAccessControlDao;
	private ISinkDao iSinkDao;
	private JdbcTemplate jdbcTemplate;
	/**
	 * 分页查询分配表数据列表
	 * @param concentratorNo 集中器号(可选)
	 * @param startDate 起始抄表时间(可选)
	 * @param endDate 终止抄表时间(可选)
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-2
	 */
	@SuppressWarnings("unchecked")
	public ResultWithPageCond<IAssignMeterDataGather> queryMeterDataPage(
			String concentratorNo, Date startDate, Date endDate,
			PageCond pageCond) throws Exception {
		Criteria listCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataGather.class);
		Criteria countCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataGather.class);
		// 所属集中器号
		if (concentratorNo != null && !"".equals(concentratorNo.trim())) {
			listCriteria.add(Restrictions.ilike("concentratorNo", concentratorNo.trim().toLowerCase(), MatchMode.ANYWHERE));
			countCriteria.add(Restrictions.ilike("concentratorNo", concentratorNo.trim().toLowerCase(), MatchMode.ANYWHERE));
		}
		// 抄表时间
		if (startDate != null && endDate == null) {
			Date startCal = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(startDate));
			listCriteria.add(Restrictions.ge("recordTime", startCal));
			countCriteria.add(Restrictions.ge("recordTime", startCal));
		} else if (startDate == null && endDate != null) {
			Date edCal = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(endDate));
			listCriteria.add(Restrictions.le("recordTime", edCal));
			countCriteria.add(Restrictions.le("recordTime", edCal));
		} else if (startDate != null && endDate != null) {
			Date startCal = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(startDate));
			Date edCal = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(endDate));
			listCriteria.add(Restrictions.between("recordTime", startCal, edCal));
			countCriteria.add(Restrictions.between("recordTime", startCal, edCal));
		}
		// 统计结果集个数
		int total = ((Long)countCriteria.setProjection(Projections.rowCount()).list().get(0)).intValue();
		// 结果集排序
		listCriteria.addOrder(Order.asc("seriousNum")).addOrder(Order.desc("recordTime"));
		// 结果集分页
		listCriteria.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
		// 构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<IAssignMeterDataGather> meterDataPage = new ResultWithPageCond<IAssignMeterDataGather>();
		meterDataPage.setList(listCriteria.list());
		meterDataPage.setPageCond(pageCond);
		return meterDataPage;
	}
	
	/**
	 * 根据分配表表号、上传时间查询分配表数据列表
	 * @param seriousNum 分配表表号(必填)
	 * @param readTime 上传时间(可选)
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-23
	 */
	@SuppressWarnings("unchecked")
	public List<IAssignMeterDataGather> queryMeterDatasByCond(String seriousNum, 
			Date readTime) throws Exception {
		String hsql = "from IAssignMeterDataGather amd " +
				"where amd.seriousNum='"+seriousNum+"'";
		if(readTime != null) {
			Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(readTime));
			Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(readTime));
			hsql += " and amd.readTime between :startTime and :endTime";
			hsql += " order by amd.readTime desc";
			Query query = this.getCurrentSession().createQuery(hsql);
			query.setTimestamp("startTime", startReadTime).setTimestamp("endTime", endReadTime);
			return query.list();
		}
		hsql += " order by amd.seriousNum asc,amd.readTime desc";
		return this.getCurrentSession().createQuery(hsql).list();
	}
	
	/**
	 * 根据分配表表号、上传时间统计分配表数据个数
	 * 
	 * 如果一块分配表的读表时间与上传时间在数据库存在则不再上传
	 * (bug) 如果7月1号抄到30号的表，而在6月30号也抄到30号的表，造成无法正常以三天上传率计算正常值
	 * @param seriousNum 分配表表号(必填)
	 * @param readTime 上传时间(可选)
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-23
	 */
	public long countMeterDataByCond(String seriousNum, Date readTime) throws Exception {
		String hsql = "select count(*) from IAssignMeterDataGather amd " +
				"where amd.seriousNum='"+seriousNum+"'";
		if(readTime != null) {
			Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(readTime));
			Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(readTime));
			hsql += " and amd.readTime between :startTime and :endTime";
			Query query = this.getCurrentSession().createQuery(hsql);
			query.setTimestamp("startTime", startReadTime).setTimestamp("endTime", endReadTime);
			return (Long) query.uniqueResult();
		}
		return (Long) this.getCurrentSession().createQuery(hsql).uniqueResult();
	}
	
	/**
	 * 根据分配表表号、上传时间删除分配表数据
	 * @param seriousNum 分配表表号(必填)
	 * @param readTime 上传时间(可选)
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-25
	 */
	public void deleteMeterDataByCond(String seriousNum, Date readTime) throws Exception {
		String hsql = "delete from IAssignMeterDataGather amd " +
				"where amd.seriousNum='"+seriousNum+"'";
		if(readTime != null) {
			Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(readTime));
			Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(readTime));
			hsql += " and amd.readTime between :startTime and :endTime";
			Query query = this.getCurrentSession().createQuery(hsql);
			query.setTimestamp("startTime", startReadTime).setTimestamp("endTime", endReadTime);
			query.executeUpdate();
		} else {
			this.runFormatHSql(hsql);
		}
	}
	
	/**
	 * 新增或修改分配表数据
	 * @param meterData
	 * 说明：新增分配表数据时，若表号相同、上传时间相同，则认为是重复数据，不予保存数据
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-25
	 */
	public ResponseMsg saveOrUpdateMeterData(IAssignMeterDataGather meterData) throws Exception {
		if(meterData.getId()!=null && !"".equals(meterData.getId())) {
			this.updateEntity(meterData, meterData.getId());
		} else {
			//查询是否存在重复数据( 该操作在recordTime与readTime 相差不为1 天或者2天时启用这个规则
			boolean flag = true;
			Date readTime = meterData.getReadTime();
			Date recordTime = meterData.getRecordTime();
			if (readTime!=null&&recordTime!=null) {
				DateTime dateTime = new DateTime(readTime);
				DateTime dateTime2 = new DateTime(recordTime);
				int i = dateTime.get(DateTimeFieldType.dayOfYear());
				int j = dateTime2.get(DateTimeFieldType.dayOfYear());
				int abs = Math.abs(i-j);
				if(abs==1||abs==2) flag=false;
				System.out.println(meterData.getSeriousNum()+"readTime  "+i+"---recordTime"+j+"---"+flag);
			}
			long repeatNum = this.countMeterDataByCond(meterData.getSeriousNum(), meterData.getReadTime());
			if(repeatNum > 0&&flag) {
				//提示重复数据
				System.out.println("数据未保存！！" +meterData.getSeriousNum());
				return ResponseMsg.getInstance(false, "相同的分配表号和上传日期，重复数据无法新增！");
			}
			//保存新数据
			this.saveEntity(meterData);
		}
		return ResponseMsg.getInstance(true, "");
	}
	
	/**
	 * 批量新增或修改分配表数据
	 * @param meterDataList
	 * 说明：新增分配表数据时，若表号相同、上传时间相同，则认为是重复数据，不予保存数据
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-25
	 */
	public ResponseMsg saveOrUpdateMeterDataBatch(List<IAssignMeterDataGather> meterDataList) throws Exception {
		if(meterDataList==null || meterDataList.size()==0) {
			return ResponseMsg.getInstance(false, "不可以使用空数据执行数据更新操作！");
		}
		ResponseMsg rmsg = ResponseMsg.getInstance(true, "");
		for(IAssignMeterDataGather meterData: meterDataList) {
			ResponseMsg tempRmsg = this.saveOrUpdateMeterData(meterData);
			//记录新增失败的提示信息
			if(!tempRmsg.isSuccess()) {
				System.out.println("新增记录失败 "+meterData.getSeriousNum());
				rmsg = tempRmsg;
			}
		}
		return rmsg;
	}
	
	/**
	 * 统计某个分摊域下对应某个上传日期的分配表数据上传率(百分比%)
	 * @param shareAreaUID 分摊域(必填)
	 * @param readTime 上传日期(必填)
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-25
	 */
	public double calcUploadPercentByShareArea(String shareAreaUID, 
			Date readTime) throws Exception {
		//统计分摊域所属参与计量收费的热用户的分配表个数
		String queryMeterNum = "select count(*) from IAssignMeter am " +
				"where am.roomId in(select hr.id from IHeatRoom hr " +
									"where hr.shareAreaID='"+shareAreaUID+"' " +
									"and hr.ifMeasureCharge=2)";
		long meterNum = (Long) this.getCurrentSession().createQuery(queryMeterNum).uniqueResult();
		//统计对应分摊域、上传日期下的参与计量收费热用户的分配表数据个数
		Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(readTime));
		Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(readTime));
		String queryMeterDataNum = "select count(*) from IAssignMeterDataGather amd " +
				"where amd.readTime between :startTime and :endTime " +
				"and amd.seriousNum in(select am.seriousNum from IAssignMeter am " +
										"where am.roomId in(select hr.id from IHeatRoom hr " +
															"where hr.shareAreaID='"+shareAreaUID+"' " +
															"and hr.ifMeasureCharge=2))";
		Query meterDataQuery = this.getCurrentSession().createQuery(queryMeterDataNum);
		meterDataQuery.setTimestamp("startTime", startReadTime).setTimestamp("endTime", endReadTime);
		long meterDataNum = (Long) meterDataQuery.uniqueResult();
		//计算对应分摊域、抄表日期的分配表数据上传率
		double uploadPercent = 0;
		if(meterDataNum > 0) {
			uploadPercent = new BigDecimal(meterDataNum)
					.divide(new BigDecimal(meterNum), new MathContext(5, RoundingMode.HALF_UP))
					.multiply(new BigDecimal("100"))
					.doubleValue();
			uploadPercent = NumberSystemConversion.toFixedDecimalScale(uploadPercent, 3);
		}
		return uploadPercent;
	}
	
	/**
	 * 统计某个集中器下对应某个上传日期的分配表数据上传率(百分比%)
	 * @param concentratorNo 集中器号(必填)
	 * @param readTime 上传日期(必填)
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-25
	 */
	public double calcUploadPercentByConcentrator(String concentratorNo, 
			Date readTime) throws Exception {
		//统计集中器所辖分配表的个数
		String queryMeterNum = "select count(*) from IAssignMeter am " +
				"where am.concentratorID=(select con.id from IConcentrator con " +
										"where con.concentratorNo='"+concentratorNo+"')";
		long meterNum = (Long) this.getCurrentSession().createQuery(queryMeterNum).uniqueResult();
		//统计对应集中器、抄表日期下的分配表数据个数
		Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(readTime));
		Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(readTime));
		String queryMeterDataNum = "select count(*) from IAssignMeterDataGather amd " +
				"where amd.concentratorNo='"+concentratorNo+"' " +
				"and amd.readTime between :startTime and :endTime";
		Query meterDataQuery = this.getCurrentSession().createQuery(queryMeterDataNum);
		meterDataQuery.setTimestamp("startTime", startReadTime).setTimestamp("endTime", endReadTime);
		long meterDataNum = (Long) meterDataQuery.uniqueResult();
		//计算对应集中器、抄表日期的分配表数据上传率
		double uploadPercent = 0;
		if(meterDataNum > 0) {
			uploadPercent = new BigDecimal(meterDataNum)
					.divide(new BigDecimal(meterNum), new MathContext(5, RoundingMode.HALF_UP))
					.multiply(new BigDecimal("100"))
					.doubleValue();
			uploadPercent = NumberSystemConversion.toFixedDecimalScale(uploadPercent, 3);
		}
		return uploadPercent;
	}

	/**
	 * 根据分摊域分页查询所属分配表数据
	 * @param shareAreaUID 分摊域唯一标识(必须)
	 * @param seriousNum 分配表表号(可选)
	 * @param readTime 上传时间(可选)
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-1
	 */
	@SuppressWarnings("unchecked")
	public ResultWithPageCond<IAssignMeterDataGather> queryMeterDataPageByAreaUID(String shareAreaUID, 
			String seriousNum, Date readTime, PageCond pageCond) throws Exception {
		//分配表数据查询初始化信息
		int total = 0;
		List<IAssignMeterDataGather> meterDataList = new ArrayList<IAssignMeterDataGather>();
		//根据所属分摊域查询分配表数据
		if(shareAreaUID!=null && !"".equals(shareAreaUID)) {
			String hsql = "from IAssignMeterDataGather amd " +
					"where amd.seriousNum in(select am.seriousNum " +
											"from IAssignMeter am " +
											"where am.roomId in(select hr.id " +
																"from IHeatRoom hr " +
																"where hr.shareAreaID='"+shareAreaUID+"'))";
			if(seriousNum!=null && !"".equals(seriousNum.trim())) {
				hsql += " and lower(amd.seriousNum) like '%"+seriousNum.trim().toLowerCase()+"%'";
			}
			if(readTime != null) {
				Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(readTime));
				Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(readTime));
				hsql += " and amd.readTime between :startTime and :endTime";
				//结果集总数查询
				String countHql = "select count(*) " + hsql;
				Query countQuery = this.getCurrentSession().createQuery(countHql);
				countQuery.setTimestamp("startTime", startReadTime).setTimestamp("endTime", endReadTime);
				total = ((Long)countQuery.uniqueResult()).intValue();
				hsql += " order by amd.readTime desc, amd.seriousNum asc";
				//分配表数据分页查询
				Query listQuery = this.getCurrentSession().createQuery(hsql);
				listQuery.setTimestamp("startTime", startReadTime).setTimestamp("endTime", endReadTime);
				listQuery.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
				meterDataList = listQuery.list();
			} else {
				//结果集总数查询
				String countHql = "select count(*) " + hsql;
				total = ((Long)this.getCurrentSession().createQuery(countHql).uniqueResult()).intValue();
				hsql += " order by amd.seriousNum asc, amd.readTime desc";
				//分配表数据分页查询
				Query query = this.getCurrentSession().createQuery(hsql);
				query.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
				meterDataList = query.list();
			}
		}
		//构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<IAssignMeterDataGather> meterDataPage = new ResultWithPageCond<IAssignMeterDataGather>();
		meterDataPage.setList(meterDataList);
		meterDataPage.setPageCond(pageCond);
		return meterDataPage;
	}
	
	/**
	 * 根据分配表ID、上传日期查询分配表数据列表
	 * @param assignMeterUID 分配表ID(必填)
	 * @param readTime 上传日期(可选)
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-23
	 */
	@SuppressWarnings("unchecked")
	public List<IAssignMeterDataGather> queryMeterDatasByMeterUID(String assignMeterUID, 
			Date readTime) throws Exception {
		String hsql = "from IAssignMeterDataGather amd " +
				"where amd.seriousNum=(select am.seriousNum " +
									"from IAssignMeter am " +
									"where am.id='"+assignMeterUID+"')";
		if(readTime != null) {
			Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(readTime));
			Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(readTime));
			hsql += " and amd.readTime between :startTime and :endTime";
			hsql += " order by amd.readTime desc";
			Query query = this.getCurrentSession().createQuery(hsql);
			query.setTimestamp("startTime", startReadTime).setTimestamp("endTime", endReadTime);
			return query.list();
		}
		hsql += " order by amd.seriousNum asc, amd.readTime desc";
		return this.getCurrentSession().createQuery(hsql).list();
	}
	
	/**
	 * 根据分配表ID查询对应的散热器权重
	 * @param assignMeterUID 分配表ID
	 * @param reviseFactor 热用户户修正系数，即散热器位置修正系数
	 * @param startReadTime 上传日期起始日期
	 * @param endReadTime 上传日期结束日期
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-4
	 */
	public double calcSinkWeightByAssignMeterUID(String assignMeterUID, double reviseFactor, 
			Date startReadTime, Date endReadTime) throws Exception {
		//计算分配表对应的散热器评价系数
		String sinkUIDQuery = "select am.heatSinkID " +
				"from IAssignMeter am " +
				"where am.id='"+assignMeterUID+"'";
		String sinkUID = (String) this.getCurrentSession().createQuery(sinkUIDQuery).uniqueResult();
		double kFactor = this.iSinkDao.calcSinkEvaluateFactor(sinkUID);
		//上传日期起始日期对应的分配表数据
		IAssignMeterDataGather startMeterData = this.queryMeterDatasByMeterUID(assignMeterUID, startReadTime).get(0);
		//上传日期结束日期对应的分配表数据
		IAssignMeterDataGather endMeterData = this.queryMeterDatasByMeterUID(assignMeterUID, endReadTime).get(0);
		//分配表读数 = 抄表日期结束日期对应的分配表数据 - 抄表日期起始日期对应的分配表数据
		double readNum = new BigDecimal(endMeterData.getReadNum())
				.subtract(new BigDecimal(startMeterData.getReadNum()))
				.doubleValue();
		//散热器权重，散热器权重 = 分配表读数 * 评价系数 * 位置修正系数或户修正系数
		double sinkWeight = new BigDecimal(readNum)
				.multiply(new BigDecimal(kFactor))
				.multiply(new BigDecimal(reviseFactor))
				.doubleValue();
		return sinkWeight;
	}
	
	/**
	 * 通过热用户唯一标识查询散热器户权重
	 * @param heatRoomUID 热用户唯一标识
	 * @param startReadTime 上传日期起始日期
	 * @param endReadTime 上传日期结束日期
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-4
	 */
	@SuppressWarnings("unchecked")
	public double calcSinkWeightByRoomUID(String heatRoomUID, Date startReadTime, 
			Date endReadTime) throws Exception {
		//查询热用户对应的分配表列表
		String meterIDsQuery = "select am.id from IAssignMeter am " +
				"where am.roomId='"+heatRoomUID+"'";
		List<Object> meterIDList = this.getCurrentSession().createQuery(meterIDsQuery).list();
		//查询该热用户对应的户修正系数，即位置修正系数
		String factorQuery = "select hr.reviseFactor " +
										"from IHeatRoom hr " +
										"where hr.id='"+heatRoomUID+"'";
		double reviseFactor = (Double)this.getCurrentSession().createQuery(factorQuery).uniqueResult();
		//户散热器权重
		BigDecimal roomSinkWeight = new BigDecimal("0");
		if(meterIDList!=null && meterIDList.size()>0) {
			for(Object meterUID: meterIDList) {
				//统计户内散热器权重
				double sinkWeight = this.calcSinkWeightByAssignMeterUID((String) meterUID, reviseFactor, startReadTime, endReadTime);
				roomSinkWeight = roomSinkWeight.add(new BigDecimal(sinkWeight));
			}
		}
		return roomSinkWeight.doubleValue();
	}
	
	/**
	 * 根据分配表表表号、上传日期查询历史数据
	 * @param seriousNum 分配表表号
	 * @param readTime 上传日期
	 * @param dataLimit 取回历史数据的条数，若不限制，则取回所有历史数据
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-14
	 */
	@SuppressWarnings("unchecked")
	public List<IAssignMeterDataGather> queryHistoryMeterData(String seriousNum, 
			Date readTime, Integer dataLimit) throws Exception {
		String hsql = "from IAssignMeterDataGather amd " +
				"where amd.seriousNum='"+seriousNum+"' ";
		Date minReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(readTime));
		hsql += "and amd.readTime<:deadline " +
				"order by amd.seriousNum asc, amd.readTime desc";
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("deadline", minReadTime);
		if(dataLimit != null) {
			query.setFirstResult(0).setMaxResults(dataLimit);
		}
		return query.list();
	}
	
	/**
	 * 新增分配表表平滑数据(包括人工干预数据和系统自动平滑数据)
	 * @param seriousNum 分配表表号
	 * @param readTime 上传时间
	 * @param readNum 分配表读数
	 * @param backTmperature 背板温度
	 * @param fontTemperature 前板温度
	 * @return 返回平滑数据的ID
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-15
	 */
	public String saveTransitionMeterData(String seriousNum, Date readTime, 
			double readNum, double backTmperature, double fontTemperature) throws Exception {
		//查询是否存在重复数据
		long repeatNum = this.countMeterDataByCond(seriousNum, readTime);
		if(repeatNum > 0) {
			//提示重复数据
			return "";
		}
		IAssignMeterDataGather assignMeterData = new IAssignMeterDataGather();
		assignMeterData.setSeriousNum(seriousNum);
		assignMeterData.setReadTime(readTime);
		assignMeterData.setReadNum(readNum);
		assignMeterData.setRecordTime(new Date()); //设置抄表日期为当前日期
		assignMeterData.setBackTmperature(backTmperature);
		assignMeterData.setFontTemperature(fontTemperature);
		String concentratorNo = seriousNum.substring(0, seriousNum.length()-3);
		assignMeterData.setConcentratorNo(concentratorNo);
		//设置平滑数据类型
		assignMeterData.setDataType(0);
		return this.saveEntity(assignMeterData);
	}
	
	/**
	 * 通过集中器上传批次任务(集中器号、抄表日期)分页查询分配表数据
	 * @param concentratorNo 任务批次上传的集中器号
	 * @param recordTime 任务批次上传的抄表日期
	 * @param seriousNum 分配表表号(可选)
	 * @param startDate 上传日期起始日期(可选)
	 * @param endDate 上传日期结束日期(可选)
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-25
	 */
	@SuppressWarnings("unchecked")
	public ResultWithPageCond<IAssignMeterDataGather> queryMeterDataPageByUploadBatch(String concentratorNo, 
			Date recordTime, String seriousNum, Date startDate, Date endDate, 
			PageCond pageCond) throws Exception {
		Criteria listCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataGather.class);
		Criteria countCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataGather.class);
		//上传任务批次设置
		listCriteria.add(Restrictions.eq("concentratorNo", concentratorNo.trim()));
		countCriteria.add(Restrictions.eq("concentratorNo", concentratorNo.trim()));
		Date startRecordTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date endRecordTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		listCriteria.add(Restrictions.between("recordTime", startRecordTime, endRecordTime));
		countCriteria.add(Restrictions.between("recordTime", startRecordTime, endRecordTime));
		//分配表表号
		if(seriousNum!=null && !"".equals(seriousNum.trim())) {
			listCriteria.add(Restrictions.ilike("seriousNum", seriousNum.trim().toLowerCase(), MatchMode.ANYWHERE));
			countCriteria.add(Restrictions.ilike("seriousNum", seriousNum.trim().toLowerCase(), MatchMode.ANYWHERE));
		}
		//上传时间
		if (startDate != null && endDate == null) {
			Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(startDate));
			listCriteria.add(Restrictions.ge("readTime", startReadTime));
			countCriteria.add(Restrictions.ge("readTime", startReadTime));
		} else if (startDate == null && endDate != null) {
			Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(endDate));
			listCriteria.add(Restrictions.le("readTime", endReadTime));
			countCriteria.add(Restrictions.le("readTime", endReadTime));
		} else if (startDate != null && endDate != null) {
			Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(startDate));
			Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(endDate));
			listCriteria.add(Restrictions.between("readTime", startReadTime, endReadTime));
			countCriteria.add(Restrictions.between("readTime", startReadTime, endReadTime));
		}
		// 统计结果集个数
		int total = ((Long)countCriteria.setProjection(Projections.rowCount()).list().get(0)).intValue();
		// 结果集排序
		listCriteria.addOrder(Order.asc("seriousNum")).addOrder(Order.desc("readTime"));
		// 结果集分页
		if(pageCond!=null) {
			listCriteria.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
		// 构造返回类型
		pageCond.setTotal(total);
		}
		ResultWithPageCond<IAssignMeterDataGather> meterDataPage = new ResultWithPageCond<IAssignMeterDataGather>();
		meterDataPage.setList(listCriteria.list());
		meterDataPage.setPageCond(pageCond);
		return meterDataPage;
	}
	
	/**
	 * 通过数据文件ID删除TXT上传的分配表数据
	 * @param dataFileUID
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-29
	 */
	public void deleteMeterDataByFileID(String dataFileUID) throws Exception {
		String hsql = "delete from IAssignMeterDataGather amd " +
				"where amd.dataFileID='"+dataFileUID+"'";
		this.runFormatHSql(hsql);
	}
	
	/**
	 * 通过TXT上传的文件ID分页查询分配表数据
	 * @param dataFileUID TXT文件ID(必选)
	 * @param seriousNum 分配表号
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-10-29
	 */
	@SuppressWarnings("unchecked")
	public ResultWithPageCond<IAssignMeterDataGather> queryMeterDataPageByFileID(String dataFileUID, 
			String seriousNum, PageCond pageCond) throws Exception {
		Criteria listCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataGather.class);
		Criteria countCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataGather.class);
		//上传任务批次设置
		listCriteria.add(Restrictions.eq("dataFileID", dataFileUID.trim()));
		countCriteria.add(Restrictions.eq("dataFileID", dataFileUID.trim()));
		//分配表表号
		if(seriousNum!=null && !"".equals(seriousNum.trim())) {
			listCriteria.add(Restrictions.ilike("seriousNum", seriousNum.trim().toLowerCase(), MatchMode.ANYWHERE));
			countCriteria.add(Restrictions.ilike("seriousNum", seriousNum.trim().toLowerCase(), MatchMode.ANYWHERE));
		}
		// 统计结果集个数
		int total = ((Long)countCriteria.setProjection(Projections.rowCount()).list().get(0)).intValue();
		// 结果集排序
		listCriteria.addOrder(Order.asc("seriousNum")).addOrder(Order.desc("readTime"));
		// 结果集分页
		listCriteria.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
		// 构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<IAssignMeterDataGather> meterDataPage = new ResultWithPageCond<IAssignMeterDataGather>();
		meterDataPage.setList(listCriteria.list());
		meterDataPage.setPageCond(pageCond);
		return meterDataPage;
	}
	
	/**
	 * 批次删除某次任务上传数据
	 * @param concentratorNo 集中器号(任务批次标识一)
	 * @param recordTime 抄表日期(任务批次标识二)
	 * @param dataTypes 上传的数据类型,若未说明,则删除所有类型的数据
	 * 0:平滑数据	1:txt上传解析数据	2:定点上传数据
	 * 3:掌机上传数据	空或其他:未知来源数据
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-12-2
	 */
	public void deleteMeterDataUploadBatch(String concentratorNo, Date recordTime, 
			Integer... dataTypes) throws Exception {
		String hsql = "delete from IAssignMeterDataGather " +
				"amd where amd.concentratorNo='"+concentratorNo+"' ";
		hsql += "and amd.recordTime between :startDate and :endDate";
		if(dataTypes!=null && dataTypes.length>0) {
			hsql += " and amd.dataType in(";
			for(int index=0; index<dataTypes.length; index++) {
				Integer dataType = dataTypes[index];
				hsql += dataType;
				if(dataTypes.length>1 && (index<dataTypes.length-1)) {
					hsql += ",";
				}
			}
			hsql += ")";
		}
		Date startDate = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date endDate = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startDate", startDate).setTimestamp("endDate", endDate);
		query.executeUpdate();
	}
	
//	/**
//	 * 根据相关任务批次、及分配表档案资料查询分配表数据的成功上传数
//	 * @param concentratorNo
//	 * @param recordTime
//	 * @param dataType
//	 * @return
//	 * @throws Exception
//	 * @author Xiaopeng
//	 * @date 2014-4-2
//	 */
//	public int queryUploadNumByReferBatch(String concentratorNo, Date recordTime, 
//			Integer dataType) throws Exception {
//		//统计档案中存在表号的抄表数据
//		String hsql = "select count(distinct amd.seriousNum) " +
//				"from IAssignMeterDataGather amd " +
//				"where amd.recordTime between :startDate and :endDate " +
//				"and amd.concentratorNo='" + concentratorNo + "' " +
//				"and lower(amd.seriousNum) in(select lower(am.seriousNum) " +
//											"from IAssignMeter am where " +
//											"am.concentratorID=(select c.id " +
//															"from IConcentrator c " +
//															"where lower(c.concentratorNo)='"+concentratorNo.toLowerCase()+"'))";
//		//数据类型
//		if(dataType != null) {
//			hsql += " and amd.dataType="+dataType;
//		}
//		Date startDate = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
//		Date endDate = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
//		Query query = this.getCurrentSession().createQuery(hsql);
//		query.setTimestamp("startDate", startDate).setTimestamp("endDate", endDate);
//		return ((Long)query.uniqueResult()).intValue();
//	}
	
	/**
	 * 查询缺少基础档案信息的分配表数据对应的表号
	 * @param concentratorNo
	 * @param recordTime
	 * @param dataType
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-4-2
	 */
	@SuppressWarnings("unchecked")
	public List<String> queryLackBaseinfoSeriousNum(String concentratorNo, Date recordTime, 
			Integer dataType) throws Exception {
		//查询档案中不存在表号的抄表数据
		String hsql = "select distinct amd.seriousNum from IAssignMeterDataGather amd " +
				"where lower(amd.concentratorNo)='"+concentratorNo.toLowerCase()+"' " +
				"and amd.recordTime between :startDate and :endDate " +
				"and lower(amd.seriousNum) not in(select lower(am.seriousNum) " +
												"from IAssignMeter am) ";
		//数据类型
		if(dataType != null) {
			hsql += " and amd.dataType="+dataType;
		}
		//结果集排序
		hsql += " order by amd.seriousNum asc";
		Date startDate = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date endDate = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startDate", startDate).setTimestamp("endDate", endDate);
		return query.list();
	}
	
	/**
	 * 根据任务批次信息查询分配表数据列表
	 * @param concentratorNo
	 * @param recordTime
	 * @param dataType
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-4-2
	 */
	@SuppressWarnings("unchecked")
	public List<IAssignMeterDataGather> queryMeterDatasByBatch(String concentratorNo, 
			Date recordTime, Integer dataType) throws Exception {
		String hsql = "from IAssignMeterDataGather amd " +
				"where lower(amd.concentratorNo)='"+concentratorNo.toLowerCase()+"' " +
				"and amd.recordTime between :startDate and :endDate";
		//数据类型
		if(dataType != null) {
			hsql += " and amd.dataType="+dataType;
		}
		//结果集排序
		hsql += " order by amd.seriousNum asc";
		Date startDate = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date endDate = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(hsql);
		query.setTimestamp("startDate", startDate).setTimestamp("endDate", endDate);
		return query.list();
	}
	
	/**
	 * 根据定制查询特征信息(所属楼栋、抄表时间)统计分配表列表数据个数
	 * @param buildingUID
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-12-10
	 */
	public int countCustomizedAmDataList(String buildingUID, Date recordTime) throws Exception {
		String countHsql = "select count(*) " +
				"from IAssignMeterDataGather amd,IAssignMeter am " +
				"where am.buildingID='"+buildingUID+"' " +
				"and amd.recordTime between :startTime and :endTime " +
				"and amd.seriousNum=am.seriousNum";
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(countHsql);
		query.setTimestamp("startTime", recordTimeStart).setTimestamp("endTime", recordTimeEnd);
		return ((Long)query.uniqueResult()).intValue();
	}
	
	/**
	 * 根据定制查询特征信息(所属楼栋、抄表时间)查询分配表数据列表
	 * @param buildingUID
	 * @param recordTime
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-12-14
	 */
	@SuppressWarnings("unchecked")
	public ResultWithPageCond<IAssignMeterDataGather> queryCustomizedAmDataList(String buildingUID, 
			Date recordTime, PageCond pageCond) throws Exception {
		String commonHsql = "from IAssignMeterDataGather amd,IAssignMeter am " +
				"where am.buildingID='"+buildingUID+"' " +
				"and amd.recordTime between :startTime and :endTime " +
				"and amd.seriousNum=am.seriousNum";
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		// 统计结果集个数
		String countHsql = "select count(amd.seriousNum) " + commonHsql;
		Query countQuery = this.getCurrentSession().createQuery(countHsql);
		countQuery.setTimestamp("startTime", recordTimeStart).setTimestamp("endTime", recordTimeEnd);
		int total = ((Long)countQuery.uniqueResult()).intValue();
		//结果集查询
		String queryHsql = "select amd " + commonHsql;
		//设置排序
		queryHsql += " order by amd.seriousNum asc";
		Query listQuery = this.getCurrentSession().createQuery(queryHsql);
		listQuery.setTimestamp("startTime", recordTimeStart).setTimestamp("endTime", recordTimeEnd);
		//设置分页
		listQuery.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
		// 构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<IAssignMeterDataGather> meterDataPage = new ResultWithPageCond<IAssignMeterDataGather>();
		meterDataPage.setList(listQuery.list());
		meterDataPage.setPageCond(pageCond);
		return meterDataPage;
	}
	
	@Override
	public ResultWithPageCond<AssignDataWithInfo> queryAssignDataWithInfo(String buildingUID, 
			Date recordTime, PageCond pageCond) throws Exception {
		ResultWithPageCond<AssignDataWithInfo> resultWithPageCond = new ResultWithPageCond<AssignDataWithInfo>();
		JdbcTemplate template = getJdbcTemplate();
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		String countsql = "SELECT COUNT(1) "
				+ "FROM (SELECT	pdata.ID,	pdata.t_concentrator_no,	pdata.t_read_num,	tempinfo.t_serious_num,	tempinfo.t_house_no,	pdata.t_front_temp,	pdata.t_back_temp "
				+ "FROM	p_assign_meter_data pdata JOIN "
				+ "(	SELECT	binfo.t_house_no, pinfo.t_serious_num	FROM		base_room_info binfo	JOIN "
				+ "(SELECT		t_serious_num, t_room_id		FROM 	p_assign_meter_info	WHERE	"
				+ "t_building_id = ?	) "
				+ "pinfo ON pinfo.t_room_id = binfo.ID) tempinfo "
				+ "ON tempinfo.t_serious_num = pdata.t_seriousNum "
				+ " WHERE	pdata.t_record_time "
				+ "BETWEEN"
				+ " ? "    //记录开始日期
				+ "AND"
				+ " ?"//记录结束日期
				+ ") AS temp;";
		//查询数据总数
		String recordSql="SELECT	pdata.ID AS id,	"
				+ "pdata.t_concentrator_no AS concentratorNo,	"
				+ "pdata.t_read_num AS readNum,	"
				+ "tempinfo.t_serious_num AS seriousNum,	"
				+ "tempinfo.t_house_no AS houseNo,	"
				+ "pdata.t_front_temp AS fontTemperature,	"
				+ "pdata.t_back_temp AS backTmperature, "
				+ "pdata.read_time AS readTime "
				+ " FROM	p_assign_meter_data pdata JOIN "
				+ " (	SELECT		binfo.t_house_no,		pinfo.t_serious_num	FROM		base_room_info binfo	JOIN"
				+ " (		SELECT			t_serious_num,	t_room_id		FROM	p_assign_meter_info		WHERE "
				+ " t_building_id =  ?	) "
				+ " pinfo ON pinfo.t_room_id = binfo.ID)"
				+ " tempinfo ON tempinfo.t_serious_num = pdata.t_seriousNum WHERE	 "
				+ " pdata.t_record_time BETWEEN "
				+ " ? "
				+ " AND"
				+ "  ?"
				+ " ORDER BY	tempinfo.t_house_no ASC ";
		String page =  " LIMIT 0, 10;";
		int limit = pageCond.getLimit();
			if(pageCond!=null&&pageCond.getStart()!=0) {
				if(limit!=0) {
					page = " LIMIT "+pageCond.getStart()+", "+pageCond.getLimit()+";";
				}
			}
			if(limit>100000) {
				page = "";
			}
				recordSql+=page;
				Integer total = template.queryForObject(countsql, new Object[] {buildingUID,recordTimeStart,recordTimeEnd}, Integer.class);
				pageCond.setTotal(total.intValue());
		RowMapper<AssignDataWithInfo> rowMapper = new RowMapper<AssignDataWithInfo>() {
			@Override
			public AssignDataWithInfo mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				AssignDataWithInfo  assignDataWithInfo = new AssignDataWithInfo();
				IAssignMeterDataGather dataGather = new IAssignMeterDataGather();
				IHeatRoom heatroom = new  IHeatRoom();
				assignDataWithInfo.setData(dataGather);
				assignDataWithInfo.setHeatRoom(heatroom);
				//将数据映射到data中
				dataGather.setId(rs.getString("id"));
				dataGather.setConcentratorNo(rs.getString("concentratorNo"));
				dataGather.setSeriousNum(rs.getString("seriousNum"));
				dataGather.setReadNum(rs.getDouble("readNum"));
				dataGather.setFontTemperature(rs.getDouble("fontTemperature"));		
				dataGather.setBackTmperature(rs.getDouble("backTmperature"));
				Timestamp timestamp = rs.getTimestamp("readTime");
				long time = timestamp.getTime();
				dataGather.setReadTime(new Date(time));
				heatroom.setHouseNo(rs.getString("houseNo"));//门牌号
				return assignDataWithInfo;
			}
		};
		List<AssignDataWithInfo> list = template.query(recordSql, new Object[] {buildingUID,recordTimeStart,recordTimeEnd}, rowMapper);
		resultWithPageCond.setList(list);
		resultWithPageCond.setPageCond(pageCond);
		return resultWithPageCond;
	}
	
	/**
	 * 查询一个批次数据的上传时间，上传时间取最大值
	 * @param concentratorNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-12-27
	 */
	public Date queryReadTime4BatchUpload(String concentratorNo, Date recordTime) throws Exception {
		String queryHsql = "select max(amd.readTime) " +
				"from IAssignMeterDataGather amd " +
				"where amd.concentratorNo='"+concentratorNo+"' " +
				"and amd.recordTime between :startTime and :endTime";
		//查询时间设置
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(recordTime);
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(recordTime);
		Query query = this.getCurrentSession().createQuery(queryHsql)
				.setTimestamp("startTime", recordTimeStart)
				.setTimestamp("endTime", recordTimeEnd);
		//结果类型处理
		Object readTimeObj = query.uniqueResult();
		if(readTimeObj != null) {
			return (Date) readTimeObj;
		}
		return null;
	}
	
	/**
	 * 根据上传批次特征信息统计分配表数据的个数
	 * @param concentratorNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-1-8
	 */
	public int countMeterDataNumByUploadBatch(String concentratorNo, Date recordTime) throws Exception {
		String queryHsql = "select count(*) from IAssignMeterDataGather amd " +
				"where amd.concentratorNo='"+concentratorNo+"' " +
				"and amd.recordTime between :startTime and :endTime";
		//查询时间设置
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		Query query = this.getCurrentSession().createQuery(queryHsql)
				.setTimestamp("startTime", recordTimeStart)
				.setTimestamp("endTime", recordTimeEnd);
		return ((Long)query.uniqueResult()).intValue();
	}
	
	
	/**
	 *查询某一分配表在某一时间段内的数据
	 * */
	@Override
	public  List<IAssignMeterDataGather> queryAssignMeterData(String  seriousNum,Date startDate,Date endDate) throws Exception{
	List<IAssignMeterDataGather> result = new ArrayList<IAssignMeterDataGather>();
		if(StringUtils.isBlank(seriousNum)) return result;
			String hql = 		"from IAssignMeterDataGather amd " +
					"where amd.seriousNum='"+seriousNum+"' " +
					"and amd.readTime between :startTime and :endTime";
			//查询时间设置
			Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(startDate);
			Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(endDate);
			//运行时 修改为getCurrentSession
			Query query = this.getCurrentSession().createQuery(hql)
//			Query query = this.getSession().createQuery(hql)
					.setTimestamp("startTime", recordTimeStart)
					.setTimestamp("endTime", recordTimeEnd);
			result =query.list();
		return result;
	}
	
	/**
	 *查询某一分配表在某一时间段内的数据,为提高效率增加结果集约束
	 * */
	@Override
	public  List<IAssignMeterDataGather> queryAssignMeterDataWithLimit(String  seriousNum,Date startDate,Date endDate,int limit) throws Exception{
			List<IAssignMeterDataGather> result = new ArrayList<IAssignMeterDataGather>();
				if(StringUtils.isBlank(seriousNum)) return result;
					String hql = 		"from IAssignMeterDataGather amd " +
							"where amd.seriousNum='"+seriousNum+"' " +
							"and amd.readTime between :startTime and :endTime";
					//查询时间设置
					Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(startDate);
					Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(endDate);
					//运行时 修改为getCurrentSession
					Query query = this.getCurrentSession().createQuery(hql)
//					Query query = this.getSession().createQuery(hql)
							.setTimestamp("startTime", recordTimeStart)
							.setTimestamp("endTime", recordTimeEnd);
					if(limit!=0) {
					query.setMaxResults(limit);//限制结果集为1行
					}
					result =query.list();
				return result;
	}
	
/**
 * 查询数据库中某一分配表号 最后一次上传时间
 * 
 * */	
@Override
public Date queryLastUploadDate(String  meterNo)  throws Exception {
	String sql = "SELECT read_time FROM p_assign_meter_data WHERE t_seriousNum = ? ORDER BY read_time DESC LIMIT 0,1;";
	if(StringUtils.isBlank(meterNo)) return null;
	Date date = null;
	try {
		 date = jdbcTemplate.queryForObject(sql, new Object[] {meterNo}, Date.class);
	} catch (Exception e) {
		return null;
	}
	return date;
}

	/**
	 * @return the sysDbName
	 */
	public String getSysDbName() {
		return sysDbName;
	}

	/**
	 * @param sysDbName the sysDbName to set
	 */
	public void setSysDbName(String sysDbName) {
		this.sysDbName = sysDbName;
	}

	/**
	 * @return the oracleDbName
	 */
	public String getOracleDbName() {
		return oracleDbName;
	}

	/**
	 * @param oracleDbName the oracleDbName to set
	 */
	public void setOracleDbName(String oracleDbName) {
		this.oracleDbName = oracleDbName;
	}

	/**
	 * @return the mysqlDbName
	 */
	public String getMysqlDbName() {
		return mysqlDbName;
	}

	/**
	 * @param mysqlDbName the mysqlDbName to set
	 */
	public void setMysqlDbName(String mysqlDbName) {
		this.mysqlDbName = mysqlDbName;
	}

	/**
	 * @return the iResourceAccessControlDao
	 */
	public IResourceAccessControlDao getiResourceAccessControlDao() {
		return iResourceAccessControlDao;
	}

	/**
	 * @param iResourceAccessControlDao the iResourceAccessControlDao to set
	 */
	public void setiResourceAccessControlDao(
			IResourceAccessControlDao iResourceAccessControlDao) {
		this.iResourceAccessControlDao = iResourceAccessControlDao;
	}

	/**
	 * @return the iSinkDao
	 */
	public ISinkDao getiSinkDao() {
		return iSinkDao;
	}

	/**
	 * @param iSinkDao the iSinkDao to set
	 */
	public void setiSinkDao(ISinkDao iSinkDao) {
		this.iSinkDao = iSinkDao;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	
	/**
	 * 根据分配表号 分页查询分配表数据
	 * 
	 * @param seriousNum 分配表号
	 * @param startDate 起始抄表时间
	 * @param endDate 终止抄表时间
	 * @param pageCond
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public ResultWithPageCond<IAssignMeterDataGather> queryMeterDataAllotPage(String seriousNum, 
			Date startDate, Date endDate, PageCond pageCond)
			throws Exception {
		Criteria listCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataGather.class);
		Criteria countCriteria = this.getCurrentSession().createCriteria(IAssignMeterDataGather.class);
		// 所属集中器号
		if (seriousNum != null && !"".equals(seriousNum.trim())) {
			listCriteria.add(Restrictions.ilike("seriousNum", seriousNum.trim().toLowerCase(), MatchMode.ANYWHERE));
			countCriteria.add(Restrictions.ilike("seriousNum", seriousNum.trim().toLowerCase(), MatchMode.ANYWHERE));
		}
		// 抄表时间
		if (startDate != null && endDate == null) {
			Date startCal = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(startDate));
			listCriteria.add(Restrictions.ge("recordTime", startCal));
			countCriteria.add(Restrictions.ge("recordTime", startCal));
		} else if (startDate == null && endDate != null) {
			Date edCal = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(endDate));
			listCriteria.add(Restrictions.le("recordTime", edCal));
			countCriteria.add(Restrictions.le("recordTime", edCal));
		} else if (startDate != null && endDate != null) {
			Date startCal = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(startDate));
			Date edCal = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(endDate));
			listCriteria.add(Restrictions.between("recordTime", startCal, edCal));
			countCriteria.add(Restrictions.between("recordTime", startCal, edCal));
		}
		// 统计结果集个数
		int total = ((Long)countCriteria.setProjection(Projections.rowCount()).list().get(0)).intValue();
		// 结果集排序
		if (seriousNum != null && !"".equals(seriousNum)) {
			listCriteria.addOrder(Order.asc("seriousNum")).addOrder(Order.desc("recordTime"));
		}
		// 结果集分页
		if (seriousNum != null && !"".equals(seriousNum)) {
			listCriteria.setFirstResult(pageCond.getStart()).setMaxResults(pageCond.getLimit());
		}
		// 构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<IAssignMeterDataGather> meterDataAllotPage = new ResultWithPageCond<IAssignMeterDataGather>();
		meterDataAllotPage.setList(listCriteria.list());
		meterDataAllotPage.setPageCond(pageCond);
		return meterDataAllotPage;
	}
	
	
	/**
	 * 根据分配表表号、上传时间查询分配表数据列表
	 * @param seriousNum 分配表表号(必填)
	 * @param readTime 上传时间(可选)
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-9-23
	 */
	@SuppressWarnings("unchecked")
	public List<IAssignMeterDataGather> queryMeterDatasBySeriousNumList(String seriousNum, 
			Date recordTime) throws Exception {
		
		if (seriousNum != null) {
			String hsql = "from IAssignMeterDataGather amd " +
					"where amd.seriousNum='"+seriousNum+"'";
			if(recordTime != null) {
				Date startReadTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
				Date endReadTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
				hsql += " and amd.recordTime between :startTime and :endTime";
				hsql += " order by amd.recordTime desc";
				Query query = this.getCurrentSession().createQuery(hsql);
				query.setTimestamp("startTime", startReadTime).setTimestamp("endTime", endReadTime);
				return query.list();
			}
			hsql += " order by amd.seriousNum asc,amd.recordTime desc";
			return this.getCurrentSession().createQuery(hsql).list();
			}
		return null;
	}
	

	
}
